package edu.lsnu.equipment_management_system.mapper.select.nopaging;

import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.damage.EquipmentDamageDetailVo;
import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.repair.EquipmentRepairVo;
import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.transfer_out.EquipmentTransferOutDetailVo;
import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo;
import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.EquipmentVo;
import edu.lsnu.equipment_management_system.pojo.vo.out.information_list.UseRecordVo;
import edu.lsnu.equipment_management_system.pojo.vo.sql_conditions.UserNoAndEquipmentNoVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author mzy
 * @Description 设备的信息获取都在这里
 * 包括详细的detail信息，缩略的basic信息，
 * 以及生命周期中所需的特定信息。
 * @date 2018/11/16
 */
public interface EquipmentInformationMapper {

    // --------------------管理员--------------------
    /**
     * @Description 管理员查所有设备的简略信息
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return
     * @params
     */
    @Select("select \n" +
            "JBXX_SB.SBBH 'equipmentNo', \n" +
            "JBXX_SB.SBMC 'equipmentName', \n" +
            "DM_SBZT.SBZT 'useStatus', \n" +
            "YHB.XM 'userName', \n" +
            "DM_CFCS.CFCS 'place', \n" +
            "JBXX_SB.DJ 'price'\n" +
            "from JBXX_SB\n" +
            "inner join DM_SBZT \n" +
            "on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "inner join \n" +
            "(\n" +
            "\tselect DISTINCT JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM\n" +
            "\tfrom JBXX_SBSY\n" +
            ") t\n" +
            "on JBXX_SB.SBBH = t.SBBH\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = t.ZGH\n" +
            "inner join DM_CFCS\n" +
            "on DM_CFCS.CFCSDM = t.CFCSDM\n")
    List<DeviceVo> getAdminAllEquipmentBasicInformationVo();
    // --------------------普通用户--------------------

    // ------------使用过和在用的简略信息------------
    /**
     * @Description 获取一个普通用户使用过的设备的简略信息
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo>
     * @params  [id] userId
     */
    @Select("select \n" +
            "JBXX_SB.SBBH 'equipmentNo', \n" +
            "JBXX_SB.SBMC 'equipmentName', \n" +
            "DM_SBZT.SBZT 'useStatus', \n" +
            "YHB.XM 'userName', \n" +
            "DM_CFCS.CFCS 'place', \n" +
            "JBXX_SB.DJ 'price'\n" +
            "from JBXX_SB\n" +
            "inner join DM_SBZT \n" +
            "on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "inner join\n" +
            "\t( -- 使用过的设备\n" +
            "\t\tselect JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM\n" +
            "\t\tfrom JBXX_SBSY\n" +
            "\t\twhere JBXX_SBSY.SBBH\n" +
            "\t\tin (\n" +
            "\t\t\tselect DISTINCT JBXX_SBSY.SBBH\n" +
            "\t\t\tfrom JBXX_SBSY\n" +
            "\t\t\twhere JBXX_SBSY.ZGH = #{userNo}\n" +
            "\t\t\t-- 不为空，即使用过的\n" +
            "\t\t\tand JBXX_SBSY.SYRQ2 is not null \n" +
            "\t\t\t-- 为空，即还在使用的\n" +
            "\t\t) and JBXX_SBSY.SYRQ2 is null\n" +
            "\t) t\n" +
            "on JBXX_SB.SBBH = t.SBBH\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = t.ZGH\n" +
            "inner join DM_CFCS\n" +
            "on DM_CFCS.CFCSDM = t.CFCSDM\n")
    List<DeviceVo> getUserUsedEquipmentBasicInformationVo(String userNo); //50426058

    /**
     * @Description 获取普通用户正在使用的设备的简略信息
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo>
     * @params  [] userNo
     */
    @Select("select \n" +
            "JBXX_SB.SBBH 'equipmentNo', \n" +
            "JBXX_SB.SBMC 'equipmentName', \n" +
            "DM_SBZT.SBZT 'useStatus', \n" +
            "YHB.XM 'userName', \n" +
            "DM_CFCS.CFCS 'place', \n" +
            "JBXX_SB.DJ 'price',\n" +
            "JBXX_SBSY_TMP.BZ 'remark'\n" +
            "from JBXX_SB\n" +
            "inner join DM_SBZT \n" +
            "on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "inner join\n" +
            "(\n" +
            "\tselect DISTINCT JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM, JBXX_SBSY.BZ\n" +
            "\tfrom JBXX_SBSY\n" +
            "\twhere JBXX_SBSY.ZGH = #{userNo}\n" +
            "\tand JBXX_SBSY.SYRQ2 is null\n" +
            ") JBXX_SBSY_TMP\n" +
            "on JBXX_SB.SBBH = JBXX_SBSY_TMP.SBBH\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "inner join DM_CFCS\n" +
            "on DM_CFCS.CFCSDM = JBXX_SBSY_TMP.CFCSDM")
    List<DeviceVo> getUserUsingEquipmentBasicInformationVo(String userNo); // 50426058

    // -------------调出和报损报废简略信息-------------
    /**
     * @Description 获取普通用户报损报废设备的简略信息
     * @author mzy
     * @time 2018-11-16
     * @see 
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo>
     * @params  [id] userNo
     */
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_BSBF.SBBH 'equipmentNo',\n" +
            "JBXX_BSBF.BSBF 'damageOr',\n" +
            "JBXX_BSBF.SFRQ 'damageDate',\n" +
            "JBXX_BSBF.SFYY 'damageReason'\n" +
            "from JBXX_BSBF\n" +
            "inner join YHB \n" +
            "on YHB.ZGH = JBXX_BSBF.ZGH\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_BSBF.SBBH\t\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "where JBXX_BSBF.ZGH = #{userNo}\n")
    List<DeviceVo> getUserDamageEquipmentBasicInformationVo(String userNo); // 50830001

    /**
     * @Description 获取普通用户调出设备的简略信息
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo>
     * @params  [id] userNo
     */
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_DRDC.SBBH 'equipmentNo',\n" +
            "\n" +
            "JBXX_DRDC.CRRQ 'transerDate',\n" +
            "JBXX_DRDC.CRYY 'transerReason',\n" +
            "JBXX_DRDC.DCDW 'transerOutUnit',\n" +
            "JBXX_DRDC.DRDW 'transerInUnit' \n" +
            "from JBXX_DRDC\n" +
            "inner join YHB \n" +
            "on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_DRDC.SBBH\t\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "where JBXX_DRDC.DCDR='调出' and JBXX_DRDC.ZGH = #{userNo}\n")
    List<DeviceVo> getUserTransferOutEquipmentBasicInformationVo(String userNo); // 50830025

    // ---------在用和使用过设备的详细信息需要；管理员也可用---------
    /**
     * @Description 通过设备编号，获取这个设备当前的详细信息
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.EquipmentVo>
     * @params  [id] equipmentNo
     */
    @Select("select \n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "DM_SBZT.SBZT 'useStatus',\n" +
            "YHB.XM 'userName',\n" +
            "DM_CFCS.CFCS 'place',\n" +
            "JBXX_SB.DJ 'price',\n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.XH 'type',\n" +
            "JBXX_SB.GG 'guiGe',\n" +
            "JBXX_SB.ZJZT 'depreciationStatus',\n" +
            "DM_SBLB.ZJNX 'depreciationPeriod',\n" +
            "JBXX_SB.CHPHM 'shippingNo',\n" +
            "JBXX_SB.GHDW 'deliveryUnit',\n" +
            "JBXX_SB.JHRQ 'purchaseDate',\n" +
            "JBXX_SB.YT 'application',\n" +
            "JBXX_SB.CCCJ 'manufacturer',\n" +
            "JBXX_SB.ZZH 'manufacturerNo',\n" +
            "JBXX_SBSY_TMP.BZ 'remark'\n" +
            "from JBXX_SB\n" +
            "left join DM_SBZT\n" +
            "on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "left join\n" +
            "(\n" +
            "\tselect JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM, JBXX_SBSY.BZ\n" +
            "\tfrom JBXX_SBSY\n" +
            "\twhere JBXX_SBSY.SYRQ2 is null\n" +
            ") JBXX_SBSY_TMP\n" +
            "on JBXX_SB.SBBH = JBXX_SBSY_TMP.SBBH\n" +
            "left join YHB\n" +
            "on YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "left join DM_CFCS\n" +
            "on DM_CFCS.CFCSDM = JBXX_SBSY_TMP.CFCSDM\n" +
            "left join DM_SBLB\n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "where JBXX_SB.SBBH = #{equipmentNo}")
    EquipmentVo getEquipmentDetailInformationVo(String equipmentNo);

    // ---------调出和损毁设备的详细信息需要---------
    /**
     * @Description 损毁设备的详细信息
     * @author mzy
     * @time 2018-11-19
     * @see
     * @return   edu.lsnu.equipment_management_system.pojo.vo.out.information_list.damage.EquipmentDamageDetailVo
     * @params  [id]
     */
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_BSBF.SBBH 'equipmentNo',\n" +
            "JBXX_BSBF.BSBF 'damageOr',\n" +
            "JBXX_BSBF.SFRQ 'damageDate',\n" +
            "JBXX_BSBF.SFYY 'damageReason',\n" +
            "\n" +
            "JBXX_SB.DJ 'price',\n" +
            "DM_SBZT.SBZT 'useStatus',\n" +
            "JBXX_SB.XH 'type',\n" +
            "JBXX_SB.GG 'guiGe',\n" +
            "DM_SBLB.ZJNX 'depreciationPeriod',\n" +
            "JBXX_SB.CHPHM 'shippingNo',\n" +
            "JBXX_SB.GHDW 'deliveryUnit',\n" +
            "JBXX_SB.JHRQ 'purchaseDate',\n" +
            "JBXX_SB.YT 'application',\n" +
            "JBXX_SB.CCCJ 'manufacturer',\n" +
            "JBXX_SB.ZZH 'manufacturerNo'\n" +
            "\n" +
            "from JBXX_BSBF\n" +
            "inner join YHB \n" +
            "on YHB.ZGH = JBXX_BSBF.ZGH\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_BSBF.SBBH\t\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join DM_SBZT\n" +
            "on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "where JBXX_BSBF.ZGH = #{userNo} and JBXX_BSBF.SBBH = #{equipmentNo}")
    EquipmentDamageDetailVo getEquipmentDamageDetailInformationVo(UserNoAndEquipmentNoVo userNoAndEquipmentNoVo); // 50830001

    /**
     * @Description 调出设备的详细信息
     * @author mzy
     * @time 2018-11-19
     * @see
     * @return   edu.lsnu.equipment_management_system.pojo.vo.out.information_list.transfer_out.EquipmentTransferOutDetailVo
     * @params  [id]
     */
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_DRDC.SBBH 'equipmentNo',\n" +
            "JBXX_DRDC.DCDR,\n" +
            "JBXX_SB.DJ 'price',\n" +
            "DM_SBZT.SBZT 'useStatus',\n" +
            "JBXX_SB.XH 'type',\n" +
            "JBXX_SB.GG 'guiGe',\n" +
            "DM_SBLB.ZJNX 'depreciationPeriod',\n" +
            "JBXX_SB.CHPHM 'shippingNo',\n" +
            "JBXX_SB.GHDW 'deliveryUnit',\n" +
            "JBXX_SB.JHRQ 'purchaseDate',\n" +
            "JBXX_SB.YT 'application',\n" +
            "JBXX_SB.CCCJ 'manufacturer',\n" +
            "JBXX_SB.ZZH 'manufacturerNo',\n" +
            "\n" +
            "JBXX_DRDC.CRRQ 'transerDate',\n" +
            "JBXX_DRDC.CRYY 'transerReason',\n" +
            "JBXX_DRDC.DCDW 'transerOutUnit',\n" +
            "JBXX_DRDC.DRDW 'transerInUnit' \n" +
            "from JBXX_DRDC\n" +
            "inner join YHB \n" +
            "on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_DRDC.SBBH\t\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join DM_SBZT\n" +
            "on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "where JBXX_DRDC.DCDR='调出' and JBXX_DRDC.ZGH = #{userNo} and JBXX_DRDC.SBBH = #{equipmentNo}")
    EquipmentTransferOutDetailVo getEquipmentTransferOutDetailInformationVo(UserNoAndEquipmentNoVo userNoAndEquipmentNoVo); // 50830025

    // ---------在用和使用过设备的生命周期需要---------
    /**
     * @Description 设备生命周期中，获取单个设备的简略信息
     * @author mzy
     * @time 2018-11-16
     * @see 
     * @return   edu.lsnu.equipment_management_system.pojo.vo.out.information_list.used_using.DeviceVo
     * @params  [id] equipmentNo
     */
    @Select("select \n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "DM_SBZT.SBZT 'useStatus',\n" +
            "YHB.XM 'userName',\n" +
            "DM_CFCS.CFCS 'place', \n" +
            "JBXX_SB.DJ 'price',\n" +
            "JBXX_SBSY_TMP.BZ 'remark'\n" +
            "from JBXX_SB\n" +
            "inner join DM_SBZT on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "inner join \n" +
            "(\n" +
            "\tselect JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.CFCSDM, JBXX_SBSY.BZ\n" +
            "\tfrom JBXX_SBSY\n" +
            "\twhere JBXX_SBSY.SYRQ2 is null\n" +
            ") JBXX_SBSY_TMP\n" +
            "on JBXX_SB.SBBH = JBXX_SBSY_TMP.SBBH\n" +
            "inner join YHB \n" +
            "on YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "inner join DM_CFCS\n" +
            "on DM_CFCS.CFCSDM = JBXX_SBSY_TMP.CFCSDM\n" +
            "where JBXX_SB.SBBH = #{equipmentNo}")
    DeviceVo getBasicInformationInLifeCycleVo(String equipmentNo);

    // ---------所有生命周期需要：一条一条的记录---------
    /**
     * @Description 设备生命周期中，具体的使用记录【列表】
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.UseRecordVo>
     * @params  [id] equipmentNo
     */
    @Select("select \n" +
            "JBXX_SBSY.ZGH 'userNo',\n" +
            "YHB.XM 'userName',\n" +
            "JBXX_SBSY.SYRQ1 'beginDate',\n" +
            "JBXX_SBSY.SYRQ2 'endDate',\n" +
            "JBXX_SBSY.YY 'reason',\n" +
            "JBXX_SBSY.BZ 'remark',\n" +
            "JBXX_SBSY.SHR 'auditor'\n" +
            "from JBXX_SBSY\n" +
            "inner join YHB on YHB.ZGH = JBXX_SBSY.ZGH\n" +
            "where JBXX_SBSY.SBBH = #{equipmentNo}\n" +
            "order by JBXX_SBSY.SYRQ1 desc\n")
    List<UseRecordVo> getUseRecordInLifeCycleVo(String equipmentNo); //20110127

    /**
     * @Description 获取待修的设备列表
     * @author mzy
     * @time 2018-11-19
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.information_list.repair.EquipmentRepairVo>
     * @params  []
     */
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBBH 'equipmentNo', \n" +
            "JBXX_SB.SBMC 'equipmentName', \n" +
            "-- DM_SBZT.SBZT 'useStatus', \n" +
            "-- YHB.XM 'userName', \n" +
            "JBXX_WHWX.WHWX 'repairOr',\n" +
            "JBXX_WHWX.XHRQ1 'repairDate',\n" +
            "JBXX_WHWX.XHYY 'repairReson',\n" +
            "JBXX_WHWX.XHDD 'repairPlace',\n" +
            "JBXX_WHWX.XHRY 'repairMan',\n" +
            "JBXX_SB.DJ 'repairMoney'\n" +
            "from JBXX_WHWX\n" +
            "inner join JBXX_SB\n" +
            "on JBXX_WHWX.SBBH = JBXX_SB.SBBH\n" +
            "inner join DM_SBZT \n" +
            "on JBXX_SB.SBZTDM = DM_SBZT.SBZTDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_WHWX.ZGH\n" +
            "inner join DM_SBLB\n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "where DM_SBZT.SBZT = '待修' \n" +
            "and  JBXX_WHWX.XHRQ2 is null \n" +
            "and JBXX_WHWX.ZGH = #{userNo}")
    List<EquipmentRepairVo> getEquipmentRepairVo(String userNo); // 50426058

    // -----------折旧年限和使用年限-----------
    /**
     * @Description 获取当前设备的使用年限
     * @author mzy
     * @time 2018-11-17
     * @see
     * @return   java.lang.Integer
     * @params  [id]
     */
    @Select("select DATEDIFF(YEAR, JBXX_SB.JHRQ, GETDATE())\n" +
            "from JBXX_SB\n" +
            "where JBXX_SB.SBBH = #{equipmentNo}")
    Integer getEquipmentUsedYear(String equipmentNo);

    /**
     * @Description 获取当前设备的折旧年限
     * @author mzy
     * @time 2018-11-17
     * @see
     * @return   java.lang.Integer
     * @params  [id]
     */
    @Select("select DM_SBLB.ZJNX 'depreciationPeriod'\n" +
            "from JBXX_SB\n" +
            "inner join DM_SBLB\n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM \n" +
            "where JBXX_SB.SBBH = #{equipmentNo}")
    Integer getEquipmentDepreciationPeriod(String equipmentNo);
}
